﻿using System.Data.Objects;
using System.Linq;
using System.Collections.Generic;
namespace CompanyDB
{
    public class CompanyDAL
    {
        #region Add

        public int AddCountry(Country country)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            entities.Countries.AddObject(country);
            entities.SaveChanges();
            return country.CountryID;
        }

        public int AddCompany(Company company)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            entities.Companies.AddObject(company);
            entities.SaveChanges();
            return company.CompanyID;
        }

        public int AddPerson(Person person)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            entities.People.AddObject(person);
            entities.SaveChanges();
            return person.PersonID;
        }

        public void AddEmployee(Employee employee)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            entities.Employees.AddObject(employee);
            entities.SaveChanges();
        }

        public int AddCustomer(Customer customer)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            using (entities)
            {
                entities.Customers.AddObject(customer);
                entities.SaveChanges();
            }
            return customer.ID;
        }

        #endregion

        #region Edit

        public void EditCustomer(Customer customer)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            using (entities)
            {
                entities.Customers.Attach(customer);
                entities.ObjectStateManager.GetObjectStateEntry(customer).SetModified();
                entities.Refresh(RefreshMode.ClientWins, customer);
                entities.SaveChanges();  
            }
        }
        #endregion

        #region Delete

        public void DeleteCustomer(Customer customer)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            using (entities)
            {
                entities.Customers.Attach(customer);
                entities.Customers.DeleteObject(customer);
                entities.SaveChanges();
            }
        }
        #endregion

        #region Read
        public Employee ReadEmployee(Employee employee)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            Employee resultEmployee;
            using (entities)
            {
                resultEmployee = (from e in entities.Employees
                                     where e.PersonID == employee.PersonID && e.CompanyID == employee.CompanyID
                                     select e).First();
                
            }
            return resultEmployee;
        }
        #endregion

        public void RaiseSalary(Company companyObj, decimal amount)
        {
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            using (entities)
            {
                entities.RaiseSalary(companyObj.Name, amount);
                entities.SaveChanges();
            }
        }

        public List<CustomersPerCompany> ReadView()
        {
            List<CustomersPerCompany> listResult = new List<CustomersPerCompany>(); 
            CompanyDatabaseNewEntities entities = new CompanyDatabaseNewEntities();
            using (entities)
            {
                var result = from r in entities.uv_ShowNumberOfCustomersPerCountry
                             select r;
                foreach (var item in result)
                {
                    listResult.Add(new CustomersPerCompany() { CompanyName = item.Country, CustomerCount = item.Customers });
                }
            }
            return listResult;
        }

        public class CustomersPerCompany
        {
            public string CompanyName { get; set; }
            public int? CustomerCount { get; set; }
        }
    }
}
